﻿using Dapper;
using Microsoft.Extensions.Logging;
using MySqlConnector;
using System.Text.Json;

namespace MiniCap.Storage
{
    public sealed class MySqlOutboxStorage
    {
        private readonly string _connStr;
        private readonly ILogger<MySqlOutboxStorage> _logger;

        public MySqlOutboxStorage(string connStr, ILogger<MySqlOutboxStorage> logger)
        {
            _connStr = connStr;
            _logger = logger;
        }

        public MySqlConnection CreateConnection() => new MySqlConnection(_connStr);

        public async Task EnsureCreatedAsync()
        {
            await using var conn = CreateConnection();
            await conn.OpenAsync();

            const string createPublished = @"
CREATE TABLE IF NOT EXISTS `minicap_published` (
  `Id` BIGINT PRIMARY KEY AUTO_INCREMENT,
  `Topic` VARCHAR(200) NOT NULL,
  `Body` LONGTEXT NULL,
  `Headers` JSON NULL,
  `Status` TINYINT NOT NULL DEFAULT 0,
  `Retries` INT NOT NULL DEFAULT 0,
  `NextTryAt` DATETIME NULL,
  `CreatedAt` DATETIME NOT NULL,
  `UpdatedAt` DATETIME NOT NULL
);";

            await conn.ExecuteAsync(createPublished);
        }

        public async Task<long> AddOutboxAsync(MySqlConnection conn, MySqlTransaction tran, string topic, string? body, IDictionary<string, string>? headers)
        {
            const string sql = @"
INSERT INTO minicap_published
(Topic, Body, Headers, Status, Retries, NextTryAt, CreatedAt, UpdatedAt)
VALUES (@Topic, @Body, @Headers, 0, 0, NULL, UTC_TIMESTAMP(), UTC_TIMESTAMP());
SELECT LAST_INSERT_ID();";

            var headersJson = headers is null ? null : JsonSerializer.Serialize(headers);
            var id = await conn.ExecuteScalarAsync<long>(sql, new { Topic = topic, Body = body, Headers = headersJson }, tran);
            return id;
        }

        public async Task<IEnumerable<OutboxRow>> FetchPendingAsync(int batch)
        {
            await using var conn = CreateConnection();
            const string sql = @"
SELECT Id, Topic, Body, Headers, Retries
FROM minicap_published
WHERE Status IN (0,2)
ORDER BY Id
LIMIT @Batch;";

            return await conn.QueryAsync<OutboxRow>(sql, new { Batch = batch });
        }

        public async Task MarkSentAsync(long id)
        {
            await using var conn = CreateConnection();
            const string sql = @"UPDATE minicap_published SET Status=1, UpdatedAt=UTC_TIMESTAMP() WHERE Id=@Id;";
            await conn.ExecuteAsync(sql, new { Id = id });
        }

        public sealed record OutboxRow(long Id, string Topic, string? Body, string? Headers, int Retries);
    }
}
